T-SQL Fundamentals by Itzik Ben-Gan

Author:Itzik Ben-Gan
Language: eng
Format: mobi, azw3, epub
Publisher: Pearson Education
Published: 2016-07-28T22:00:00+00:00

This query returns the following output:

country numlocations

--------------- ------------

Argentina 1

Austria 2

Belgium 2

Brazil 4

Canada 3

Denmark 2

Finland 2

France 9

Germany 11

Ireland 1

Italy 3

Mexico 1

Norway 1

Poland 1

Portugal 1

Spain 3

Sweden 2

Switzerland 2

UK 2

USA 14

Venezuela 4

(21 row(s) affected)

This query demonstrates how to group the result of a UNION operator; similarly, you can, of course, apply other logical-query phases in the outer query.

Remember that the ORDER BY clause is not allowed in the input queries. What if you need to restrict the number of rows in those queries with the TOP or OFFSET-FETCH filter? Again, you can resolve this problem with table expressions. Recall that an ORDER BY clause is allowed in an inner query with TOP or OFFSET-FETCH. In such a case, the ORDER BY clause serves only the filtering-related purpose and has no presentation meaning. For example, the following code uses TOP queries to return the two most recent orders for employees 3 and 5:

Click here to view code image

SELECT empid, orderid, orderdate

FROM (SELECT TOP (2) empid, orderid, orderdate

FROM Sales.Orders

WHERE empid = 3

ORDER BY orderdate DESC, orderid DESC) AS D1

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.